import openpyxl
from copy import copy


def find_first_empty_row(sheet, column=1):
    """查找指定列的第一个空行（从第1行开始检查）"""
    for row in range(1, sheet.max_row + 2):  # +2 防止全列有数据时越界
        if sheet.cell(row=row, column=column).value is None:
            return row
    return sheet.max_row + 1  # 如果全部非空，返回最后一行+1


def merge_excel_keep_format(file1_path, file2_path, output_path):
    # 加载两个工作簿
    wb1 = openpyxl.load_workbook(file1_path)
    wb2 = openpyxl.load_workbook(file2_path)

    # 遍历所有共同的 sheet
    common_sheets = set(wb1.sheetnames) & set(wb2.sheetnames)
    common_sheets.remove("封面")

    for sheet_name in common_sheets:
        ws1 = wb1[sheet_name]
        ws2 = wb2[sheet_name]

        # 获取 ws2 的数据（跳过前两行）
        data_rows = list(ws2.iter_rows(min_row=3, values_only=True))

        # 找到 ws1 的第一列（A列）第一个空行（而不是直接用 max_row）
        max_row_ws1 = find_first_empty_row(ws1, column=1)

        # 追加数据（保留格式）
        for row_idx, row_data in enumerate(data_rows, start=max_row_ws1):
            for col_idx, value in enumerate(row_data, start=1):
                ws1.cell(row=row_idx, column=col_idx, value=value)

                # 复制 ws2 的格式（可选）
                src_cell = ws2.cell(row=row_idx - max_row_ws1 + 3, column=col_idx)  # +3 因为跳过前2行
                dest_cell = ws1.cell(row=row_idx, column=col_idx)

                if src_cell.has_style:
                    dest_cell.font = copy(src_cell.font)
                    dest_cell.border = copy(src_cell.border)
                    dest_cell.fill = copy(src_cell.fill)
                    dest_cell.alignment = copy(src_cell.alignment)
                    dest_cell.number_format = copy(src_cell.number_format)

    # 保存合并后的文件
    wb1.save(output_path)
    print(f"合并完成，结果已保存到: {output_path}")


"""
适用于小数据量
"""
if __name__ == '__main__':
    file1 = r"D:\家宽\2025-08\鸡泽39004G.xlsx"
    file2 = r"D:\家宽\2025-08\鸡泽59004G.xlsx"
    output = r"D:\家宽\2025-08\鸡泽批配置合并.xlsx"

    merge_excel_keep_format(file1, file2, output)
